from django.db.models.signals import post_syncdb
from django.db import connection
import settings


def createAssociationTables(sender, **kwargs):
    for key, value in settings.TAGGABLE_MODELS.iteritems():    
    #create association tables for any relationships that don't already exist
        try:
        #check if table exists. If not, create it.
            cursor = connection.cursor()
#            sql = "SELECT id from magictags_tag_users"
            sql = "SELECT id from magictags_tag_"+key
            cursor.execute(sql)
        except:
            cursor = connection.cursor()
#            example_sql = """CREATE TABLE magictags_tag_users (
#                    id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
#                    tag_id integer NOT NULL,
#                    user_id integer NOT NULL,
#                    UNIQUE(tag_id, user_id)
#                    );
#                    
#                    ALTER TABLE magictags_tag_users ADD CONSTRAINT magictags_tag_users_tag_fk FOREIGN KEY (tag_id) REFERENCES magictags_tag (id);
#                    ALTER TABLE magictags_tag_users ADD CONSTRAINT magictags_tag_users_users_fk FOREIGN KEY (user_id) REFERENCES auth_user (id);
#            """
                    
            #get the model's table name for foreign key purposes
            fk_table_name = value.replace('.','_')
            model_name = value.split('.')[1]
            primary_key = model_name + '_id'
            
            
            sql = """CREATE TABLE magictags_tag_"""+key+""" (
                                id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
                                tag_id integer NOT NULL,
                                """+primary_key+""" integer NOT NULL,
                                UNIQUE(tag_id, """+primary_key+""")
                                );
                                
                                ALTER TABLE magictags_tag_"""+key+""" ADD CONSTRAINT magictags_tag_"""+key+"""_tag_fk FOREIGN KEY (tag_id) REFERENCES magictags_tag (id);
                                ALTER TABLE magictags_tag_"""+key+""" ADD CONSTRAINT magictags_tag_"""+key+"""_"""+key+"""_fk FOREIGN KEY ("""+primary_key+""") REFERENCES """+fk_table_name+""" (id);
                        """
            cursor.execute(sql)

#listen for syncdb signal
post_syncdb.connect(createAssociationTables)